package com.service.database.action;

import com.service.database.handler.AbstractDatabaseHandler;
import com.service.database.model.DataColumn;
import com.service.database.config.DataBase;
import org.springframework.util.Assert;

import java.util.ArrayList;
import java.util.List;

/**
 * SQL语句适配器
 * @author xianggj
 * @Date 2021/6/4 13:39
 **/
public class SqlAdapter {

    /**
     * 数据库
     */
    private DataBase dataBase;

    /**
     * 表名
     */
    private String tableName;

    /**
     * 表名
     */
    private String dbName;

    /**
     * 模式
     */
    private String schema;

    /**
     * 列
     */
    private String[] cols;

    /**
     * 主键
     */
    private String columnKey;

    /**
     * 返回字符串
     */
    private List<String> returnKeys = new ArrayList<>();

    public SqlAdapter() {
        this.dataBase = DataBase.MYSQL;
    }

    public SqlAdapter setDataBase(DataBase dataBase){
        Assert.notNull(dataBase, "数据源类型不能为null");
        this.dataBase = dataBase;
        return this;
    }

    public SqlAdapter setTableName(String tableName) {
        this.tableName = tableName;
        return this;
    }

    public SqlAdapter setSchema(String schema) {
        this.schema = schema;
        return this;
    }

    public SqlAdapter setDbName(String dbName) {
        this.dbName = dbName;
        return this;
    }

    public SqlAdapter setCols(String... cols) {
        this.cols = cols.clone();
        return this;
    }

    public SqlAdapter setColumnKey(String columnKey) {
        this.columnKey = columnKey;
        return this;
    }

    /**
     * 可指定任意值 不能重复 根据返回数量决定添加几个
     * @param returnKey
     */
    public void addReturnKeys(String returnKey) {
        this.returnKeys.add(returnKey);
    }

    public List<String> getReturnKeys() {
        return returnKeys;
    }

    /**
     * 查询数据库列表sql
     * @return
     */
    public String getShowDataBaseSql(){
        addReturnKeys("baseName");
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setReturnKeys(this.returnKeys);
        return handler.getShowDataBaseSql();
    }

    /**
     * 查询数据库列表sql
     * @return
     */
    public String getShowTablesSql(){
        addReturnKeys("tableName");
        addReturnKeys("comment");
        addReturnKeys("createTime");
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setReturnKeys(this.returnKeys);
        return handler.getShowTablesSql();
    }

    /**
     * 查询数据库表详情sql
     * @return
     */
    public String getShowTableDescSql(){
        //列名称
        addReturnKeys("columnName");
        //列类型
        addReturnKeys("dataType");
        //是否为空
        addReturnKeys("isNull");
        //是否主键
        addReturnKeys("columnKey");
        //列长度
        addReturnKeys("length");
        //小数点长度
        addReturnKeys("floatLength");
        //列注释
        addReturnKeys("columnComment");
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        handler.setReturnKeys(this.returnKeys);
        return handler.getShowTableDescSql();
    }

    /**
     * 查询数据库表详情sql
     * @return
     */
    public String getShowTableDataSql(){
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        return handler.getShowTableDataSql();
    }

    /**
     * 查询数据库表详情sql
     * @return
     */
    public String getShowTableDataNumSql(){
        //列名称
        addReturnKeys("total");
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        handler.setReturnKeys(this.returnKeys);
        return handler.getShowTableDataNumSql();
    }
    /**
     * 查询数据库表详情sql
     * @return
     */
    public String distinctColSql(String colName){
        //列名称
        addReturnKeys(colName);
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        handler.setCols(cols);
        handler.setReturnKeys(this.returnKeys);
        return handler.distinctColSql();
    }

    /**
     * 查询数据库表详情sql
     * @return
     */
    public String distinctColsSql(){
        //列名称
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        handler.setCols(cols);
        handler.setReturnKeys(this.returnKeys);
        return handler.distinctColSql();
    }

    /**
     * 添加分页信息
     * @param sql
     * @param num
     * @param offset
     * @return
     */
    public String addLimitBySql(String sql , Integer num, Integer offset){
        AbstractDatabaseHandler handler = dataBase.getHandler();
        return handler.addLimitBySql(sql, num, offset);
    }

    /**
     * 删除表sql
     * @return
     */
    public String dropTableSql(){
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        return handler.dropTableSql();
    }

    /**
     * 创建表sql
     *  有些库是多句
     * @return
     */
    public List<String> createTableSql(List<DataColumn> columns){
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        return handler.createTableSql(columns);
    }

    public String createInsertSQL(){
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        handler.setCols(cols);
        return handler.createInsertSQL();
    }

    public String createReplaceSQL(){
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        handler.setCols(cols);
        return handler.createReplaceSQL();
    }

    public String createUpdateSQL(){
        AbstractDatabaseHandler handler = dataBase.getHandler();
        handler.setSchema(schema);
        handler.setTableName(tableName);
        handler.setDbName(dbName);
        handler.setCols(cols);
        handler.setColumnKey(columnKey);
        return handler.createUpdateSQL();
    }


    /**
     * 获取查询Schema的SQL
     * @return
     */
    public String getSchemaSQL(String schema){
        return String.format("SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = '%s')", schema);
    }

}
